//
//  CYDataBaseHandle.m
//  UI_豆瓣更新版
//
//  Created by dllo on 16/1/8.
//  Copyright © 2016年 lanou3g. All rights reserved.
//

#import "CYDataBaseHandle.h"

@implementation CYDataBaseHandle

+ (CYDataBaseHandle *)sharedDataBaseHandle {
    // 静态修饰指针
    static CYDataBaseHandle *handle = nil;
    // 线程只执行一次
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        handle = [[CYDataBaseHandle alloc] init];
    });
    return handle;
}

- (void)openDB {
    // 沙河路径
    NSString *document = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    // 拼接数据库路径
    NSString *dbPath = [document stringByAppendingPathComponent:@"douban.sqlite"];
    NSLog(@"沙盒 : %@", dbPath);
    int result = sqlite3_open(dbPath.UTF8String, &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    } else {
        NSLog(@"打开失败");
    }
}

- (void)createTable {
    // sql 语句
    NSString *sqlStr = [NSString stringWithFormat:@"create table if not exists Activity(number integer primary key autoincrement, title text, image text, participant_count text, wisher_count text, end_time text, begin_time text, address text, owner text, category_name text, content text)"];
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"建表成功");
    } else {
        NSLog(@"建表失败");
    }
}

- (void)insertModel:(ActivityModel *)model {
    // sql语句
    NSString *sqlStr = [NSString stringWithFormat:@"insert into Activity(title, image, participant_count, wisher_count, end_time, begin_time, address, owner, category_name, content) values('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')", model.title, model.image, model.participant_count, model.wisher_count, model.end_time, model.begin_time, model.address, model.owner, model.category_name, model.content];
    // 执行sql语句
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }
}

- (void)deleteModel:(ActivityModel *)model {
    // sql语句
    NSString *sqlStr = [NSString stringWithFormat:@"delete from Activity where title = '%@'", model.title];
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
}

- (void)deleteAll {
    // sql语句
    NSString *sqlStr = [NSString stringWithFormat:@"delete from Activity"];
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"清除缓存");
    } else {
        NSLog(@"清除失败");
    }
}

- (void)updateTable:(ActivityModel *)model {
    // sql 语句
    NSString *sqlStr = [NSString stringWithFormat:@"update Activity set address = '%@', owner = '%@' where title = '%@'", model.address, model.owner, model.title];
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"更新成功");
    } else {
        NSLog(@"更新失败");
    }
}

- (NSMutableArray *)selectTable {
    // sql语句
    NSString *sqlStr = [NSString stringWithFormat:@"select * from Activity"];
    sqlite3_stmt *stmt = nil; /** 跟随指针 */
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL); /** 进入查询 */
    NSMutableArray *arrModel = [NSMutableArray array];
    if (result == SQLITE_OK) {
        // 遍历
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *image = sqlite3_column_text(stmt, 2);
            const unsigned char *participant_count = sqlite3_column_text(stmt, 3);
            const unsigned char *wisher_count = sqlite3_column_text(stmt, 4);
            const unsigned char *end_time = sqlite3_column_text(stmt, 5);
            const unsigned char *begin_time = sqlite3_column_text(stmt, 6);
            const unsigned char *address = sqlite3_column_text(stmt, 7);
            const unsigned char *owner = sqlite3_column_text(stmt, 8);
            const unsigned char *category_name = sqlite3_column_text(stmt, 9);
            const unsigned char *content = sqlite3_column_text(stmt, 10);
            
            ActivityModel *model = [ActivityModel activityModel];
            model.title = [NSString stringWithUTF8String:(const char *)title];
            model.image = [NSString stringWithUTF8String:(const char *)image];
            model.participant_count = [NSString stringWithUTF8String:(const char *)participant_count];
            model.wisher_count = [NSString stringWithUTF8String:(const char *)wisher_count];
            model.end_time = [NSString stringWithUTF8String:(const char *)end_time];
            model.begin_time = [NSString stringWithUTF8String:(const char *)begin_time];
            model.address = [NSString stringWithUTF8String:(const char *)address];
            model.owner = [NSString stringWithUTF8String:(const char *)owner];
            model.category_name = [NSString stringWithUTF8String:(const char *)category_name];
            model.content = [NSString stringWithUTF8String:(const char *)content];
            [arrModel addObject:model];
        }
    } else {
        NSLog(@"查询失败");
    }
    return arrModel;
}

@end













